using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using PropertyManagement.Core.Model;

namespace PropertyManagement.Core.DataAccess {
        public static partial class BillMonthyMappingDataAccess {
             
            public static BillMonthyMapping GetById(int id){
                var sql = @"select * from dbo.BillMonthyMapping where BillMonthyMappingID = @id ";
                var paramList = new[] {
                    new SqlParameter("id", id)
                };
                using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationBillMonthyMappingByIReader(iReader);
                    };
                    return null;
                }
            }

            public static void Add(BillMonthyMapping entity){
                  var sql = @"insert into dbo.BillMonthyMapping values (@billmonth,@feebillid,@houseownerid,@billtype,@creationdate,@lastupdate,@creationuserid,@lastactionuserid) ;   select SCOPE_IDENTITY (); ";
                  var paramList = new SqlParameter[] {
							new SqlParameter("billmonth",entity.BillMonth),
							new SqlParameter("billtype",entity.BillType),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("feebillid",entity.FeeBillID),
							new SqlParameter("houseownerid",entity.HouseOwnerID),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate)
                        };
                  using ( var ireader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                            paramList)) {
                        if (ireader.Read()) {
                            entity.BillMonthyMappingID = int.Parse(ireader[0].ToString());
                        }
                  }
            
            }   

            public static void Remove(BillMonthyMapping entity) {
                var sql = @"delete from dbo.BillMonthyMapping where BillMonthyMappingID = @id";
                var paramList = new[] {
                    new SqlParameter("id", entity.BillMonthyMappingID)
                };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }   

            public static IEnumerable<BillMonthyMapping> LoadAll(){
                    var sql = " select * from dbo.BillMonthyMapping ";
                    var list = new List<BillMonthyMapping>( );
                    using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql)) {
                        while (iReader.Read( )) {
                                list.Add(CreationBillMonthyMappingByIReader(iReader));
                        };
                    }  
                    return list;
            }   
        
            public static void Update(BillMonthyMapping entity){
                var sql = @" update dbo.BillMonthyMapping set BillMonth=@billmonth,BillType=@billtype,CreationDate=@creationdate,CreationUserID=@creationuserid,FeeBillID=@feebillid,HouseOwnerID=@houseownerid,LastActionUserID=@lastactionuserid,LastUpdate=@lastupdate where BillMonthyMappingID = @id ";
                var paramList = new SqlParameter[] {
                            new SqlParameter("id",entity.BillMonthyMappingID),
							new SqlParameter("billmonth",entity.BillMonth),
							new SqlParameter("billtype",entity.BillType),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("feebillid",entity.FeeBillID),
							new SqlParameter("houseownerid",entity.HouseOwnerID),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate)
                        };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }
            
            public static bool Exists(BillMonthyMapping entity){
                return entity.BillMonthyMappingID != 0 && GetById(entity.BillMonthyMappingID)!=null;
            }


            private static BillMonthyMapping CreationBillMonthyMappingByIReader(IDataReader iReader){
                if(iReader!=null){
                      return new BillMonthyMapping{
							BillMonth = iReader["BillMonth"] is DBNull ? default(DateTime) : (DateTime)iReader["BillMonth"] ,
							BillMonthyMappingID = iReader["BillMonthyMappingID"] is DBNull ? default(Int32) : (Int32)iReader["BillMonthyMappingID"] ,
							BillType = iReader["BillType"] is DBNull ? default(BillType) : (BillType)iReader["BillType"] ,
							CreationDate = iReader["CreationDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CreationDate"] ,
							CreationUserID = iReader["CreationUserID"] is DBNull ? default(Int32) : (Int32)iReader["CreationUserID"] ,
							FeeBillID = iReader["FeeBillID"] is DBNull ? default(Int32) : (Int32)iReader["FeeBillID"] ,
							HouseOwnerID = iReader["HouseOwnerID"] is DBNull ? default(Int32) : (Int32)iReader["HouseOwnerID"] ,
							LastActionUserID = iReader["LastActionUserID"] is DBNull ? default(Int32) : (Int32)iReader["LastActionUserID"] ,
							LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"] 
                        };
                }
                return null; 
            }

            
            
            public static BillMonthyMapping GetByPKs(BillMonthyMapping entity){
                var sql =  @"select *  from dbo.BillMonthyMapping where BillMonth = @billmonth and HouseOwnerID = @houseownerid and BillType = @billtype ";
                var paramList = new SqlParameter[] {
							new SqlParameter("billmonth",entity.BillMonth),
							new SqlParameter("houseownerid",entity.HouseOwnerID),
							new SqlParameter("billtype",entity.BillType)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationBillMonthyMappingByIReader(iReader);
                    };
                    return null;
                }
            }

            public static bool ExistsByPKs(BillMonthyMapping entity){
                var sql =  @"select *  from dbo.BillMonthyMapping where BillMonth = @billmonth and HouseOwnerID = @houseownerid and BillType = @billtype ";
                var paramList = new SqlParameter[] {
							new SqlParameter("billmonth",entity.BillMonth),
							new SqlParameter("houseownerid",entity.HouseOwnerID),
							new SqlParameter("billtype",entity.BillType)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    return iReader.Read( );
                }
            }

    }
}